
do "E:/ReplicateBuild/02_code/00_environment/00_set_environment.do"

*Table of Contents
local ccData = 1
	local newGeocode = 1
local reportCardData = 1
	local dataCheck = 1

	

********************************************************************************
* Read in Common Core data
********************************************************************************

if `ccData' == 1 {
	********************************************************************************
	* Create dataset of Common Core school characteristics by year
	* Identifier: ncerdc_lea ncerdc_schlcode sy
	********************************************************************************
	
	forvalues yy=2011/2017 {
	import sas using "$rawdata/School/Public School Universe/ccdpsu`yy'.sas7bdat", clear
	
	cap drop LEAID
	cap ren lea LEA
	cap ren schlcode SCHLCODE
	
	foreach var in "totDISADV" { // hiding variable name
		capture confirm variable `var'
		if !_rc {
						   
				   }
				   else {
						   gen `var' = .
				   }
	}

	ren (LEA SCHLCODE MAGNET TITLEISTAT SCHNAM LEANM LSTREE LCITY LSTATE LZIP LZIP4 GSLO GSHI MEMBER AM ASIAN BLACK HISP PACIFIC TR WHITE TOTETH FTE totDISADV NCESSCH ) ///
		(ncerdc_lea ncerdc_schlcode magnet titleI school_name lea_name street1 city state zip zip4 grade_low grade_high tot_enroll amer_ind asian black hisp pacific ///
		two_races white tot_enroll_ethnicity fte tot_DISADV ncessch)
	keep ncerdc_lea ncerdc_schlcode magnet titleI school_name lea_name street1 city state zip zip4 grade_low grade_high tot_enroll amer_ind asian black hisp pacific ///
		two_races white tot_enroll_ethnicity fte tot_DISADV ncessch
	
	foreach var in "amer_ind" "asian" "black" "hisp" "pacific" "two_races" "white" {
	    gen frac_`var' = `var'/tot_enroll_ethnicity
		drop `var'
	}
	drop tot_enroll_ethnicity

	assert state=="NC"
	
	gen sy = `yy'
	
	tempfile temp`yy'
	save `temp`yy'', replace
	
	}
	
	clear
	
	forvalues yy=2011/2017 {
		append using `temp`yy''
	}
	
	
	destring ncessch, replace
	
	// we have one duplicates row for the Cherokee Elementary and High Schools
	drop if sy==2011 & lea_name=="CHEROKEE ELEMENTARY SCHOOL"
	gen changeobs = (sy==2011 & lea_name=="CHEROKEE HIGH SCHOOL")
	replace lea_name = "CHEROKEE ELEMENTARY AND HIGH SCHOOL" if changeobs==1
	replace school_name = "CHEROKEE ELEMENTARY AND HIGH SCHOOL" if changeobs==1
	replace grade_low = "KG" if changeobs==1
	replace grade_high = "12" if changeobs==1
	drop changeobs
	
	ren magnet magnet_str
	gen magnet = (magnet_str=="1") if magnet_str!="M"
	drop magnet_str
	
	ren titleI titleI_str
	gen titleI_eligible = (titleI_str!="6") if titleI_str!="M"
	gen titleI = titleI_eligible
	replace titleI = 0 if (titleI_str=="1" | titleI_str=="4")
	drop titleI_str
	
	sort ncerdc_lea ncerdc_schlcode sy
	unique ncerdc_lea ncerdc_schlcode sy
	
	duplicates report ncerdc_lea ncerdc_schlcode sy
	
	tempfile tempccd
	save `tempccd', replace
	
	
	if `newGeocode' == 1 {
	    import excel using "$auxdata/ccd_locations.xlsx", clear
		ren (A F G H) (ncessch state ccd_latitude ccd_longitude)
		keep if state=="NC"
		drop if ccd_latitude=="." | ccd_latitude=="" | ccd_longitude=="." | ccd_longitude=="" | strpos(ccd_latitude,"NN")>0 | strpos(ccd_longitude,"NN")>0
		drop state B C D E
		sort ncessch
		tempfile tempccdgeo
		save `tempccdgeo', replace
		
		use `tempccd', clear

		keep ncessch ncerdc_lea ncerdc_schlcode street1 city state zip zip4
		duplicates drop 
		gen fulladdress = street1 + ", " + city + ", " + state + " " + zip
		replace fulladdress = fulladdress + "-" + zip4 if zip4!=""
		
		sort ncessch
		merge n:1 ncessch using `tempccdgeo'
		drop if _m==2
		drop _m
		opencagegeo if ccd_latitude=="" | ccd_longitude=="", key($key) fulladdress(fulladdress) replace 	
		
		gen school_latitude = ccd_latitude
		gen school_longitude = ccd_longitude
		replace school_latitude = g_lat if (ccd_latitude=="" | ccd_longitude=="") & g_quality>=6
		replace school_longitude = g_lon if (ccd_latitude=="" | ccd_longitude=="") & g_quality>=6
		
		/* Look up rest manually: latlong.net and Google Maps

		fulladdress
		35 BACON'S WAY, WHITEVILLE, NC 28472
		274 HUSKY TRAIL, TARBORO, NC 27886
		154 FOUNDATION COURT, MOORESVILLE, NC 28117
		400FLEMINGTON DR., LAKE WACCAMAW, NC 28450
		400FLEMINGTON DR, LAKE WACCAMAW, NC 28450
		2260 ACHIEVEMENT AVE SE, BOLIVIA, NC 28422
		123 NOBLITT DRIVE, CAMDEN, NC 27921
		123 NOBLITT DR, CAMDEN, NC 27921
		21 CAMPUS CIRCLE, MURPHY, NC 28906
		1401SUMMIT AVENUE, GREENSBORO, NC 27405
		329 COLLEGE RD, GREENSBORO, NC 27410
		102 CSB DR, WINTON, NC 27986
		54 COLLEGE DR, MARION, NC 28752
		54 COLLEGE DRIVE, MARION, NC 28752
		15425 SCHOLASTIC LN, CHARLOTTE, NC 28277-4506
		2790 LANDSCAPE DR, NEW BERN, NC 28562
		2790 LANDSCAPE DRIVE, NEW BERN, NC 28562
		132 STATESIDE BLVD, JACKSONVILLE, NC 28546
		132 STATESIDE BLVD, JACKSONVILLE, NC 28546-3551
		132 STATESIDE BLVD., JACKSONVILLE, NC 28546-3551
		132 STATESIDE BLVD., JACKSONVILLE, NC 28546
		377 MITZPAH RD, ROCKINGHAM, NC 28379


		*/

		replace school_latitude = "34.310230" if fulladdress=="35 BACON'S WAY, WHITEVILLE, NC 28472"
		replace school_longitude = "-78.712330" if fulladdress=="35 BACON'S WAY, WHITEVILLE, NC 28472"
		
		replace school_latitude = "35.891670" if fulladdress=="274 HUSKY TRAIL, TARBORO, NC 27886"
		replace school_longitude = "-77.580180" if fulladdress=="274 HUSKY TRAIL, TARBORO, NC 27886"
				
		replace school_latitude = "35.541100" if fulladdress=="154 FOUNDATION COURT, MOORESVILLE, NC 28117"
		replace school_longitude = "-80.864330" if fulladdress=="154 FOUNDATION COURT, MOORESVILLE, NC 28117"
				
		replace school_latitude = "34.318280" if fulladdress=="400FLEMINGTON DR., LAKE WACCAMAW, NC 28450"
		replace school_longitude = "-78.524230" if fulladdress=="400FLEMINGTON DR., LAKE WACCAMAW, NC 28450"
				
		replace school_latitude = "34.318280" if fulladdress=="400FLEMINGTON DR, LAKE WACCAMAW, NC 284502"
		replace school_longitude = "-78.524230" if fulladdress=="400FLEMINGTON DR, LAKE WACCAMAW, NC 28450"
				
		replace school_latitude = "33.973543" if fulladdress=="2260 ACHIEVEMENT AVE SE, BOLIVIA, NC 28422"
		replace school_longitude = "-78.198888" if fulladdress=="2260 ACHIEVEMENT AVE SE, BOLIVIA, NC 28422"
				
		replace school_latitude = "36.331904" if fulladdress=="123 NOBLITT DRIVE, CAMDEN, NC 27921"
		replace school_longitude = "-76.182878" if fulladdress=="123 NOBLITT DRIVE, CAMDEN, NC 27921"
				
		replace school_latitude = "36.331904" if fulladdress=="123 NOBLITT DR, CAMDEN, NC 27921"
		replace school_longitude = "-76.182878" if fulladdress=="123 NOBLITT DR, CAMDEN, NC 27921"

		replace school_latitude = "35.068002" if fulladdress=="21 CAMPUS CIRCLE, MURPHY, NC 28906"
		replace school_longitude = "-83.9656910" if fulladdress=="21 CAMPUS CIRCLE, MURPHY, NC 28906"
				
		replace school_latitude = "36.095190" if fulladdress=="1401SUMMIT AVENUE, GREENSBORO, NC 27405"
		replace school_longitude = "-79.770868" if fulladdress=="1401SUMMIT AVENUE, GREENSBORO, NC 27405"
				
		replace school_latitude = "36.081461" if fulladdress=="329 COLLEGE RD, GREENSBORO, NC 27410"
		replace school_longitude = "-79.894256" if fulladdress=="329 COLLEGE RD, GREENSBORO, NC 27410"
				
		replace school_latitude = "36.386683" if fulladdress=="102 CSB DR, WINTON, NC 27986"
		replace school_longitude = "-76.939249" if fulladdress=="102 CSB DR, WINTON, NC 27986"
				
		replace school_latitude = "35.655848" if fulladdress=="54 COLLEGE DR, MARION, NC 28752"
		replace school_longitude = "-81.962037" if fulladdress=="54 COLLEGE DR, MARION, NC 28752"
				
		replace school_latitude = "35.655848" if fulladdress=="54 COLLEGE DRIVE, MARION, NC 287522"
		replace school_longitude = "-81.962037" if fulladdress=="54 COLLEGE DRIVE, MARION, NC 28752"
				
		replace school_latitude = "35.037500" if fulladdress=="15425 SCHOLASTIC LN, CHARLOTTE, NC 28277-4506"
		replace school_longitude = "-80.856011" if fulladdress=="15425 SCHOLASTIC LN, CHARLOTTE, NC 28277-4506"
				
		replace school_latitude = "35.027461" if fulladdress=="2790 LANDSCAPE DR, NEW BERN, NC 28562"
		replace school_longitude = "-77.021452" if fulladdress=="2790 LANDSCAPE DR, NEW BERN, NC 28562"
				
		replace school_latitude = "35.027461" if fulladdress=="2790 LANDSCAPE DRIVE, NEW BERN, NC 28562"
		replace school_longitude = "-77.021452" if fulladdress=="2790 LANDSCAPE DRIVE, NEW BERN, NC 28562"
				
		replace school_latitude = "34.856079" if fulladdress=="132 STATESIDE BLVD, JACKSONVILLE, NC 28546"
		replace school_longitude = "-77.480547" if fulladdress=="132 STATESIDE BLVD, JACKSONVILLE, NC 28546"
				
		replace school_latitude = "34.856079" if fulladdress=="132 STATESIDE BLVD, JACKSONVILLE, NC 28546-3551"
		replace school_longitude = "-77.480547" if fulladdress=="132 STATESIDE BLVD, JACKSONVILLE, NC 28546-3551"
				
		replace school_latitude = "34.856079" if fulladdress=="132 STATESIDE BLVD., JACKSONVILLE, NC 28546-3551"
		replace school_longitude = "-77.480547" if fulladdress=="132 STATESIDE BLVD., JACKSONVILLE, NC 28546-3551"
				
		replace school_latitude = "34.856079" if fulladdress=="132 STATESIDE BLVD., JACKSONVILLE, NC 28546"
		replace school_longitude = "-77.480547" if fulladdress=="132 STATESIDE BLVD., JACKSONVILLE, NC 28546"
				
		replace school_latitude = "34.897041" if fulladdress=="377 MITZPAH RD, ROCKINGHAM, NC 28379"
		replace school_longitude = "-79.784261" if fulladdress=="377 MITZPAH RD, ROCKINGHAM, NC 28379"
		

		sort ncessch ncerdc_lea ncerdc_schlcode street1 city state zip zip4
		
		save "$auxdata/schoolGeocodes", replace

	}
	
	use `tempccd', clear
	
	
	sort  ncessch ncerdc_lea ncerdc_schlcode street1 city state zip zip4
	
	merge n:1  ncessch ncerdc_lea ncerdc_schlcode street1 city state zip zip4 using "$auxdata/schoolGeocodes", keepusing(school_latitude school_longitude)
	assert _m==3
	drop _m
	
	drop street1 city state zip zip4
	
	sort ncerdc_lea ncerdc_schlcode sy

	
	compress
	
	save "$basedata/ncerdc_ccd", replace
}



********************************************************************************
* Read in Report Card data
********************************************************************************

if `reportCardData' == 1 {
	********************************************************************************
	* Create dataset of school report cards by year
	* Identifier: ncerdc_lea ncerdc_schlcode sy
	********************************************************************************
	
	forvalues yy=10/13 {
		local yyminus1 = `yy'-1
		if `yy'==10 {
			local yyminus1 = "09"
		}
		import sas using "$rawdata/School/SchoolReportCard/schlrptfinal`yyminus1'_`yy'.sas7bdat", clear
		cap ren lea LEA
		cap ren year YEAR
		ren (LEA SCHLCODE YEAR) (ncerdc_lea ncerdc_schlcode sy)
		destring sy, replace
		
		replace sy = 20`yy'
		
		cap destring POVERTY_TOP_QUART POVERTY_BOT_QUART CLASS_HIGHQUAL_PCT AYP_TARGETMET_PCT INTERNET_CLASSROOM_PCT SAT_PARTICIPATION_PCT TEACH_EMER_PROV_PCT TEACH_LATERAL_PCT, replace
		cap destring *_GLVL_PCT ENROLL_WFD_PCT ENROLL_APIB_PCT, replace
		
		tempfile temp20`yy'
		save `temp20`yy'', replace
	}
	
	forvalues yy=14/16 {
		local yyminus1 = `yy'-1
		import sas using "$rawdata/School/SchoolReportCard/20`yy'/schlrptfinal`yyminus1'_`yy'.sas7bdat", clear
		
		foreach var in "YEAR" {
		capture confirm variable `var'
		if !_rc {
						   
				   }
				   else {
						   gen `var' = 20`yy'
				   }
		}
		
		cap ren lea LEA
		cap drop year
		cap ren schlcode SCHLCODE
		ren (LEA SCHLCODE YEAR) (ncerdc_lea ncerdc_schlcode sy)
		destring sy, replace
		
		cap destring POVERTY_TOP_QUART POVERTY_BOT_QUART CLASS_HIGHQUAL_PCT AYP_TARGETMET_PCT INTERNET_CLASSROOM_PCT SAT_PARTICIPATION_PCT TEACH_EMER_PROV_PCT TEACH_LATERAL_PCT, replace
		cap destring *_GLVL_PCT ENROLL_WFD_PCT ENROLL_APIB_PCT, replace
		
		tempfile temp20`yy'
		save `temp20`yy'', replace
	}
	
	forvalues yy=2017/2017 {
		
		tempfile temp`yy'
		local i = 0
		
		foreach dname in "environ" "personnel" "profile" "rta" "sce" "spg" "sus" "targets" "yoe" {
		import sas using "$rawdata/School/SchoolReportCard/`yy'/schlrpt_`dname'`yy'.sas7bdat", clear
		
		cap ren lea LEA
		cap ren schlcode SCHLCODE
		ren (LEA SCHLCODE) (ncerdc_lea ncerdc_schlcode)
		
		sort ncerdc_lea ncerdc_schlcode

		
		if `i'>0 {
			merge 1:1 ncerdc_lea ncerdc_schlcode using `temp`yy''
			drop _m
		}
		
		local i = `i'+1
		sort ncerdc_lea ncerdc_schlcode
		save `temp`yy'', replace
		
		}
		
		gen sy = `yy'
		
		
		save `temp`yy'', replace
	}
	
	forvalues yy=2018/2018 {
		
		tempfile temp`yy'

		import sas using "$rawdata/School/SchoolReportCard/`yy'/pub_src18.sas7bdat", clear
		
		cap ren lea LEA
		cap ren schlcode SCHLCODE
		ren (LEA SCHLCODE) (ncerdc_lea ncerdc_schlcode)
		
		sort ncerdc_lea ncerdc_schlcode

		gen sy = `yy'
		
		
		save `temp`yy'', replace
	}

	clear
	forvalues yy=2010/2018 {
		append using `temp`yy''
	}

	* stitch together some school characteristics
	
	* attendance rate: missing for 2016
	gen attendance_rate = AVG_DAILY_ATTEND_PCT if sy>=2010 & sy<=2013
	replace attendance_rate = pc_ATTEND/100 if sy>=2014 & sy<=2015
	replace attendance_rate = sch_avd_att_pct if sy==2017
	replace attendance_rate = pct_att/100 if sy==2018
	
	* spg grade
	gen school_evaas_rating = ""
	replace school_evaas_rating = EVASgwth if sy>=2014 & sy<=2016 // Exceeded, Met, NotMet
	replace school_evaas_rating = acad_growth if sy==2017 // Exceeded, Met, Not Met
	replace school_evaas_rating = eg_status if sy==2018 // Exceeded, Met, NotMet
	replace school_evaas_rating = "Not Met" if school_evaas_rating=="NotMet"
	
	gen school_exceeded = (school_evaas_rating=="Exceeded")
	gen school_met = (school_evaas_rating=="Met")
	gen school_notmet = (school_evaas_rating=="NotMet")
	

	destring ncerdc_lea, force replace
	destring ncerdc_schlcode, force replace
	drop if ncerdc_lea==.
	
	sort ncerdc_lea ncerdc_schlcode sy
	duplicates report ncerdc_lea ncerdc_schlcode sy
	
	compress
	
	save "$basedata/ncerdc_report_cards", replace
 
}